-- @owner: zhanghuan
-- @date: 2024-06-19
-- @testpoint: 多参数不同数据类型，部分步骤合理报错

--step1:创建表; expect:执行成功
DROP TABLE IF EXISTS t_ustore_listagg_0028;
CREATE TABLE t_ustore_listagg_0028(
     COL_1 integer,
     COL_2 bigint,
     COL_3 float8,
     COL_4 decimal(12,6),
     COL_5 bool,
     COL_6 char(30),
     COL_7 varchar2(50),
     COL_8 varchar(30),
     COL_9 interval day to second,
     COL_10 TIMESTAMP,
     COL_11 date,
     COL_12 SMALLDATETIME,
     COL_13 TIMESTAMP WITHOUT TIME ZONE,
     COL_14 blob,
     COL_15 clob,
     COL_16 int[]
) WITH (STORAGE_TYPE=USTORE);

--step2:创建序列; expect:执行成功
drop sequence if exists seq_ustore_listagg_0028;
create sequence seq_ustore_listagg_0028 increment by 1 start with 10;

--step3:向表中插入数据; expect:执行成功
insert into t_ustore_listagg_0028 values(1,seq_ustore_listagg_0028.nextval,   1+445.255,98*0.99,  true,lpad('abc','30','@'),lpad('abc','30','b'),rpad('abc','30','e'),(INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
to_timestamp('2019-01-03','YYYY-MM-DD '),to_timestamp('2019-01-03','YYYY-MM-DD '),to_timestamp('2019-01-03','YYYY-MM-DD '),lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0028 values(1,seq_ustore_listagg_0028.nextval,   1+445.255,98*0.99,  true,lpad('abc','30','@'),lpad('abc','30','b'),rpad('abc','30','e'),(INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
to_timestamp('2019-01-03','YYYY-MM-DD '),to_timestamp('2019-01-03','YYYY-MM-DD '),to_timestamp('2019-01-03','YYYY-MM-DD '),lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0028 values(1,seq_ustore_listagg_0028.nextval,   1+445.255,98*0.99,  true,lpad('abc','30','@'),lpad('abc','30','b'),rpad('abc','30','e'),(INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
to_timestamp('2019-01-03','YYYY-MM-DD '),to_timestamp('2019-01-03','YYYY-MM-DD '),to_timestamp('2019-01-03','YYYY-MM-DD '),lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0028 values(1,seq_ustore_listagg_0028.nextval,   1+445.255,98*0.99,  true,lpad('abc','30','@'),lpad('abc','30','b'),rpad('abc','30','e'),(INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
 to_timestamp('2019-01-03','YYYY-MM-DD '),to_timestamp('2019-01-03','YYYY-MM-DD '),to_timestamp('2019-01-03','YYYY-MM-DD '),lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0028 values(1,seq_ustore_listagg_0028.nextval,   1+445.255,98*0.99,  true,lpad('abc','30','@'),lpad('abc','30','b'),rpad('abc','30','e'),(INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
to_timestamp('2019-01-03','YYYY-MM-DD '),to_timestamp('2019-01-03','YYYY-MM-DD '),to_timestamp('2019-01-03','YYYY-MM-DD '),lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_listagg_0028 values(1,seq_ustore_listagg_0028.nextval,   1+445.255,98*0.99,  true,lpad('abc','30','@'),lpad('abc','30','b'),rpad('abc','30','e')::blob,(INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
to_timestamp('2019-01-03','YYYY-MM-DD '),to_timestamp('2019-01-03','YYYY-MM-DD '),to_timestamp('2019-01-03','YYYY-MM-DD '),lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');

--step4:多参数不同数据类型; expect:执行成功
select col_1,listagg('swqeqwe','rrrrrrrrrrrrrrrrrrrr') within group (order by col_1,col_7,col_8,col_3) from t_ustore_listagg_0028 group by col_1 order by 1,2;

--step5:多参数不同数据类型; expect:合理报错
select col_1,listagg(1,14,'swqeqwe','rrrrrrrrrrrrrrrrrrrr',342342.435435) within group (order by col_1,col_2,col_7,col_8,col_3) from t_ustore_listagg_0028 group by col_1 order by 1,2;

--step6:清理环境; expect:执行成功
drop sequence if exists seq_ustore_listagg_0028;
drop table if exists t_ustore_listagg_0028;
